package com.lw.lost.daos

import android.content.ContentValues
import android.database.Cursor
import android.database.sqlite.SQLiteDatabase
import com.lw.lost.models.Comment
import java.text.SimpleDateFormat
import java.util.Date
import java.util.Locale

class PostDao(private val db: SQLiteDatabase) {

    fun insert(userId: Long, content: String, imageUrl: String? = null): Long {
        val currentTime = getCurrentTime()
        val values = ContentValues().apply {
            put("userId", userId)
            put("content", content)
            put("imageUrl", imageUrl)
            put("status", 0)
            put("createTime", currentTime)
            put("updateTime", currentTime)
            put("likeCount", 0)
            put("commentCount", 0)
        }
        return db.insert("posts", null, values)
    }

    fun getAllPosts(): Cursor {
        return db.rawQuery("""
            SELECT 
                p.postId,
                p.userId,
                p.content,
                p.imageUrl,
                p.status,
                p.createTime,
                p.likeCount,
                p.commentCount,
                u.name as userName,
                u.avatar as userAvatar
            FROM posts p
            LEFT JOIN users u ON p.userId = u.userId
            WHERE p.status = 0
            ORDER BY p.createTime DESC
        """, null)
    }

    fun getAllActivePosts(): Cursor {
        return db.rawQuery("""
            SELECT p.*, u.name as userName, u.avatar as userAvatar 
            FROM posts p
            LEFT JOIN users u ON p.userId = u.userId
            WHERE p.status = 0
            ORDER BY p.createTime DESC
        """, null)
    }

    fun searchPosts(keyword: String): Cursor {
        val searchPattern = "%$keyword%"
        return db.rawQuery("""
            SELECT p.*, u.name as userName, u.avatar as userAvatar 
            FROM posts p
            LEFT JOIN users u ON p.userId = u.userId
            WHERE p.content LIKE ? AND p.status = 0
            ORDER BY p.createTime DESC
        """, arrayOf(searchPattern))
    }

    fun updatePostStatus(postId: Long, status: Int): Int {
        val values = ContentValues().apply {
            put("status", status)
            put("updateTime", getCurrentTime())
        }
        return db.update(
            "posts",
            values,
            "postId = ?",
            arrayOf(postId.toString())
        )
    }

    fun updateLikeCount(postId: Long, increment: Boolean): Int {
        val cursor = db.query(
            "posts",
            arrayOf("likeCount"),
            "postId = ?",
            arrayOf(postId.toString()),
            null,
            null,
            null
        )

        return if (cursor.moveToFirst()) {
            val currentLikes = cursor.getInt(0)
            val newLikes = if (increment) currentLikes + 1 else currentLikes - 1
            cursor.close()

            val values = ContentValues().apply {
                put("likeCount", newLikes)
                put("updateTime", getCurrentTime())
            }
            db.update(
                "posts",
                values,
                "postId = ?",
                arrayOf(postId.toString())
            )
        } else {
            cursor.close()
            0
        }
    }

    fun toggleLike(postId: Long, userId: Long): Pair<Int, Boolean> {
        val isLiked = isLikedByUser(postId, userId)
        
        return try {
            db.beginTransaction()
            
            if (isLiked) {
                // 取消点赞
                db.delete(
                    "post_likes",
                    "postId = ? AND userId = ?",
                    arrayOf(postId.toString(), userId.toString())
                )
                
                // 更新帖子点赞数
                val cursor = db.query(
                    "posts",
                    arrayOf("likeCount"),
                    "postId = ?",
                    arrayOf(postId.toString()),
                    null,
                    null,
                    null
                )
                
                if (cursor.moveToFirst()) {
                    val currentLikes = cursor.getInt(0)
                    cursor.close()
                    
                    // 确保点赞数不会小于0
                    val newLikes = maxOf(0, currentLikes - 1)
                    
                    val values = ContentValues().apply {
                        put("likeCount", newLikes)
                        put("updateTime", getCurrentTime())
                    }
                    val result = db.update(
                        "posts",
                        values,
                        "postId = ?",
                        arrayOf(postId.toString())
                    )
                    
                    db.setTransactionSuccessful()
                    Pair(result, false)
                } else {
                    cursor.close()
                    Pair(0, false)
                }
            } else {
                // 添加点赞
                val likeValues = ContentValues().apply {
                    put("postId", postId)
                    put("userId", userId)
                }
                db.insert("post_likes", null, likeValues)
                
                // 更新帖子点赞数
                val cursor = db.query(
                    "posts",
                    arrayOf("likeCount"),
                    "postId = ?",
                    arrayOf(postId.toString()),
                    null,
                    null,
                    null
                )
                
                if (cursor.moveToFirst()) {
                    val currentLikes = cursor.getInt(0)
                    cursor.close()
                    
                    val postValues = ContentValues().apply {
                        put("likeCount", currentLikes + 1)
                        put("updateTime", getCurrentTime())
                    }
                    val result = db.update(
                        "posts",
                        postValues,
                        "postId = ?",
                        arrayOf(postId.toString())
                    )
                    
                    db.setTransactionSuccessful()
                    Pair(result, true)
                } else {
                    cursor.close()
                    Pair(0, false)
                }
            }
        } catch (e: Exception) {
            Pair(0, false)
        } finally {
            db.endTransaction()
        }
    }

    fun isLikedByUser(postId: Long, userId: Long): Boolean {
        return db.query(
            "post_likes",
            null,
            "postId = ? AND userId = ?",
            arrayOf(postId.toString(), userId.toString()),
            null,
            null,
            null
        ).use { cursor ->
            cursor.count > 0
        }
    }

    fun like(postId: Long, userId: Long): Boolean {
        return try {
            db.beginTransaction()
            
            // 检查是否已经点赞
            if (!isLikedByUser(postId, userId)) {
                // 插入点赞记录
                val values = ContentValues().apply {
                    put("postId", postId)
                    put("userId", userId)
                }
                db.insert("post_likes", null, values)
                
                // 更新帖子点赞数为实际点赞记录数
                db.execSQL("""
                    UPDATE posts 
                    SET likeCount = (
                        SELECT COUNT(*) 
                        FROM post_likes 
                        WHERE postId = ?
                    ),
                    updateTime = ? 
                    WHERE postId = ?
                """, arrayOf(postId, getCurrentTime(), postId))
                
                db.setTransactionSuccessful()
                true
            } else {
                db.setTransactionSuccessful()
                false
            }
        } catch (e: Exception) {
            false
        } finally {
            db.endTransaction()
        }
    }

    fun unlike(postId: Long, userId: Long): Boolean {
        return try {
            db.beginTransaction()
            
            // 检查是否已经点赞
            if (isLikedByUser(postId, userId)) {
                // 删除点赞记录
                db.delete(
                    "post_likes",
                    "postId = ? AND userId = ?",
                    arrayOf(postId.toString(), userId.toString())
                )
                
                // 更新帖子点赞数为实际点赞记录数
                db.execSQL("""
                    UPDATE posts 
                    SET likeCount = (
                        SELECT COUNT(*) 
                        FROM post_likes 
                        WHERE postId = ?
                    ),
                    updateTime = ? 
                    WHERE postId = ?
                """, arrayOf(postId, getCurrentTime(), postId))
                
                db.setTransactionSuccessful()
                true
            } else {
                db.setTransactionSuccessful()
                false
            }
        } catch (e: Exception) {
            false
        } finally {
            db.endTransaction()
        }
    }

    private fun getCurrentTime(): String {
        return SimpleDateFormat("yyyy-MM-dd HH:mm:ss", Locale.getDefault())
            .format(Date())
    }

    // 添加评论
    fun addComment(postId: Long, userId: Long, content: String): Boolean {
        return try {
            db.beginTransaction()
            
            // 插入评论记录
            val values = ContentValues().apply {
                put("postId", postId)
                put("userId", userId)
                put("content", content)
            }
            db.insert("post_comments", null, values)
            
            // 更新帖子评论数
            db.execSQL("""
                UPDATE posts 
                SET commentCount = (
                    SELECT COUNT(*) 
                    FROM post_comments 
                    WHERE postId = ?
                ),
                updateTime = ? 
                WHERE postId = ?
            """, arrayOf(postId, getCurrentTime(), postId))
            
            db.setTransactionSuccessful()
            true
        } catch (e: Exception) {
            false
        } finally {
            db.endTransaction()
        }
    }

    // 获取帖子的评论列表
    fun getComments(postId: Long): List<Comment> {
        val comments = mutableListOf<Comment>()
        
        val cursor = db.rawQuery("""
            SELECT 
                c.commentId,
                c.postId,
                c.userId,
                c.content,
                c.createTime,
                u.name as userName,
                u.avatar as userAvatar
            FROM post_comments c
            LEFT JOIN users u ON c.userId = u.userId
            WHERE c.postId = ?
            ORDER BY c.createTime DESC
        """, arrayOf(postId.toString()))
        
        cursor.use {
            while (it.moveToNext()) {
                comments.add(
                    Comment(
                        commentId = it.getLong(it.getColumnIndexOrThrow("commentId")),
                        postId = it.getLong(it.getColumnIndexOrThrow("postId")),
                        userId = it.getLong(it.getColumnIndexOrThrow("userId")),
                        userName = it.getString(it.getColumnIndexOrThrow("userName")),
                        userAvatar = it.getString(it.getColumnIndexOrThrow("userAvatar")),
                        content = it.getString(it.getColumnIndexOrThrow("content")),
                        createTime = it.getString(it.getColumnIndexOrThrow("createTime"))
                    )
                )
            }
        }
        
        return comments
    }
} 